-- 1. piecemeal restore with partial database availability
USE master;
GO

IF DB_ID('Partitioning') IS NOT NULL
BEGIN
	ALTER DATABASE Partitioning SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE Partitioning;
END
GO 

-- look at the BackupSizeInBytes column
RESTORE FILELISTONLY FROM DISK = 'C:\MSSQL\Backup\Partitioning.bak';
GO

-- restore the PRIMARY filegroup from the full database backup,
/*
it contains the PRIMARY filegroup only since SECONDARY is readonly
note the NORECOVERY option to restore the backup log afterwards
*/
RESTORE DATABASE Partitioning READ_WRITE_FILEGROUPS
	FROM DISK = 'C:\MSSQL\Backup\Partitioning.bak' WITH PARTIAL, NORECOVERY,
	MOVE 'Partitioning_dat' TO 'C:\MSSQL\Data\Primary\Partitioning.mdf',
	MOVE 'Partitioning_log' TO 'C:\MSSQL\Data\Primary\Partitioning.ldf';

-- PRIMARY is not online yet
SELECT [name], [state_desc] FROM Partitioning.sys.database_files;
GO

-- restore the log file(s) for PRIMARY
--RESTORE LOG Partitioning
--FROM DISK = 'C:\MSSQL\Backup\Partitioning.trn' WITH NORECOVERY;
--GO

RESTORE LOG Partitioning
FROM DISK = 'C:\MSSQL\Backup\Partitioning.trn' WITH NORECOVERY;
GO

-- PRIMARY is still not online
SELECT [name], [state_desc] FROM Partitioning.sys.database_files;
GO

-- and then finalize recovery for PRIMARY
RESTORE DATABASE Partitioning WITH RECOVERY;
GO

-- now PRIMARY is online, SECONDARY is still in pending recovery...
SELECT [name], [state_desc] FROM Partitioning.sys.database_files;
GO

USE Partitioning;
GO

-- select records from the PRIMARY filegroup only (orderdate > 2010-01-01)
SELECT * FROM dbo.Orders WHERE orderdate > '01/01/2010';

-- select records from both filegroups, PRIMARY and SECONDARY (orderdate > 2009-01-01)
-- raises an error since SECONDARY is not online yet
SELECT * FROM dbo.Orders WHERE orderdate > '01/01/2009';

-- restore the SECONDARY filegroup of the database from the SECONDARY filegroup backup
USE master;
GO

-- note the NORECOVERY option to restore the backup log(s) for SECONDARY afterwards
RESTORE DATABASE Partitioning 
	FILEGROUP = 'SECONDARY'
FROM DISK = 'C:\MSSQL\Backup\Secondary.bak' WITH NORECOVERY;
GO

-- SECONDARY is restoring...
SELECT [name], [state_desc] FROM Partitioning.sys.database_files;
GO

-- restore the backup log(s) for SECONDARY
-- since we don't have any, SECONDARY was read-only, reapply the log for PRIMARY
RESTORE LOG Partitioning
FROM DISK = 'C:\MSSQL\Backup\Partitioning.trn' WITH NORECOVERY;
GO

-- SECONDARY is still restoring...
SELECT [name], [state_desc] FROM Partitioning.sys.database_files;
GO

-- now finalize recovery for the dataase
RESTORE DATABASE Partitioning WITH RECOVERY;
GO

-- now both Primary and Secondary are online
SELECT [name], [state_desc] FROM Partitioning.sys.database_files;
GO

-- 2. filegroup restore
USE master;
GO

IF DB_ID('Partitioning') IS NOT NULL
BEGIN
	ALTER DATABASE Partitioning SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE Partitioning;
END
GO 

-- restore the PRIMARY filegroup from the primary filegroup backup
RESTORE DATABASE Partitioning
	FILEGROUP = 'PRIMARY' FROM DISK = 'C:\MSSQL\Backup\Primary.bak' WITH NORECOVERY,
	MOVE 'Partitioning_dat' TO 'C:\MSSQL\Data\Primary\Partitioning.mdf',
	MOVE 'Partitioning_Archive' TO 'C:\MSSQL\Data\Secondary\Partitioning.ndf',
	MOVE 'Partitioning_log' TO 'C:\MSSQL\Data\Primary\Partitioning.ldf';

RESTORE FILELISTONLY FROM DISK = 'C:\MSSQL\Backup\Secondary.bak';
GO

-- restore the SECONDARY filegroup from the secondary filegroup backup
RESTORE DATABASE Partitioning 
	FILEGROUP = 'SECONDARY'
FROM DISK = 'C:\MSSQL\Backup\Secondary.bak' WITH NORECOVERY;
GO

-- restore the log file
RESTORE LOG Partitioning
FROM DISK = 'C:\MSSQL\Backup\Log.trn' WITH NORECOVERY;
GO

-- and then finalize recovery
RESTORE DATABASE Partitioning WITH RECOVERY;
GO

-- database is online
SELECT [name], [state_desc] FROM Partitioning.sys.database_files;
GO